#!/bin/bash
# todo 定义变量：数据同步日期，表示同步哪一条日志数据
# todo 第1、执行shell脚本时，传递参数
# todo 第2、如果没有传递参数，同步前一天数据

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

# todo 指标计算语句
ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL="
INSERT OVERWRITE TABLE jtp_app_ads.ads_traffic_page_view_report
SELECT *
FROM jtp_app_ads.ads_traffic_page_view_report
UNION
SELECT '${data_date}'                                                as dt,
       1                                                           as recent_days,
       province,
       city,
       sum(session_pv)                                             as page_view_count,
       count(DISTINCT device_id)                                   as user_count,
       count(session_id)                                           as session_count,
       round(count(session_id) / count(DISTINCT device_id), 2)     as avg_user_session_count,
       round(sum(session_during_time) / 1000 / 60, 2)              as session_during_time,
       round(avg(session_during_time) / 1000 / 60, 2)              as avg_session_during_time,
       sum(if(session_pv = 1, 1, 0))                               as bounce_count,
       round(sum(if(session_pv = 1, 1, 0)) / count(session_pv), 4) as bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt >= date_sub('${data_date}', 1 - 1)
  AND dt <= '${data_date}'
  AND province IS NOT NULL
  AND city IS NOT NULL
GROUP BY province, city
UNION ALL
SELECT '${data_date}'                                                as dt,
       3                                                           as recent_days,
       province,
       city,
       sum(session_pv)                                             as page_view_count,
       count(DISTINCT device_id)                                   as user_count,
       count(session_id)                                           as session_count,
       round(count(session_id) / count(DISTINCT device_id), 2)     as avg_user_session_count,
       round(sum(session_during_time) / 1000 / 60, 2)              as session_during_time,
       round(avg(session_during_time) / 1000 / 60, 2)              as avg_session_during_time,
       sum(if(session_pv = 1, 1, 0))                               as bounce_count,
       round(sum(if(session_pv = 1, 1, 0)) / count(session_pv), 4) as bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt >= date_sub('${data_date}', 3 - 1)
  AND dt <= '${data_date}'
  AND province IS NOT NULL
  AND city IS NOT NULL
GROUP BY province, city
UNION ALL
SELECT '${data_date}'                                                as dt,
       7                                                           as recent_days,
       province,
       city,
       sum(session_pv)                                             as page_view_count,
       count(DISTINCT device_id)                                   as user_count,
       count(session_id)                                           as session_count,
       round(count(session_id) / count(DISTINCT device_id), 2)     as avg_user_session_count,
       round(sum(session_during_time) / 1000 / 60, 2)              as session_during_time,
       round(avg(session_during_time) / 1000 / 60, 2)              as avg_session_during_time,
       sum(if(session_pv = 1, 1, 0))                               as bounce_count,
       round(sum(if(session_pv = 1, 1, 0)) / count(session_pv), 4) as bounce_count_rate
FROM jtp_app_dws.dws_event_log_session_agg
WHERE dt >= date_sub('${data_date}', 7 - 1)
  AND dt <= '${data_date}'
  AND province IS NOT NULL
  AND city IS NOT NULL
GROUP BY province, city;
SELECT * FROM jtp_app_ads.ads_traffic_page_view_report;
"

#todo 执行语句
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${ADS_TRAFFIC_PAGE_VIEW_REPORT_SQL}"